﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;

using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;
using System.IO;

namespace AutoTestingFramework.Utilities
{
    /// <summary>
    /// A wrapper of <see cref="OpenXmlExcelHelper"/>
    /// </summary>
    public class ExcelHelper2
    {
        //1. Read excel data into DataTable
        //2. Import datatable data into excel
        //3. Read special cell value or set this cell some value, so this function should accept coordinates of this cell
        #region OpenXml
        private static OpenXmlExcelHelper _openXmlExcelHelper = new OpenXmlExcelHelper();


        public static void Open(string filePath)
        {
            if (string.IsNullOrWhiteSpace(filePath))
                throw new ArgumentNullException("filePath");
            _openXmlExcelHelper.ExcelName(filePath, false);
        }
        public static DataTable ReadData(string sheetName = null, ExcelDataRange dataRange = null)
        {

            sheetName = string.IsNullOrWhiteSpace(sheetName)
                        ? "Sheet1"
                        : sheetName;

            dataRange = dataRange ?? new ExcelDataRange();

            _openXmlExcelHelper.SelectWorkSheetByName(sheetName);

            return _openXmlExcelHelper.GetDataInDataSet(dataRange.ColumnBeginIndex,
                                                       dataRange.ColumnEndIndex,
                                                       dataRange.RowBeginIndex,
                                                       dataRange.RowEndIndex,
                                                       dataRange.IsFirstRowHeader);

        }
        #endregion

        public static DataTable ReadExcelData(string path, string sheetName = null)
        {
            DataTable dt = new DataTable();
            dt.TableName = "TestData";

            XSSFWorkbook workbook = null;

            using (FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read))
            {
                workbook = new XSSFWorkbook(fs);
            }

            sheetName = string.IsNullOrEmpty(sheetName) ? "Sheet1" : sheetName;

            if (workbook != null)
            {
                XSSFSheet sheet = workbook.GetSheet(sheetName) as XSSFSheet;
                //Take the first row as header
                XSSFRow headerRow = sheet.GetRow(0) as XSSFRow;
                //Set column name
                int colCount = 0;
                for (int i = headerRow.FirstCellNum; i < headerRow.LastCellNum; i++)
                {
                    ICell cell = headerRow.GetCell(i);
                    if (cell == null)
                    {
                        break;
                    }

                    DataColumn col = new DataColumn(headerRow.GetCell(i).ToString());
                    dt.Columns.Add(col);
                    colCount++;
                }

                for (int j = 1; j <= sheet.LastRowNum; j++)
                {
                    XSSFRow row = sheet.GetRow(j) as XSSFRow;
                    DataRow dr = dt.NewRow();
                    for (int k = 0; k < colCount; k++)
                    {
                        ICell dtCell = row.GetCell(k);
                        if (dtCell!=null)
                        {
                            dr[k] = dtCell.StringCellValue;
                        }
                    }

                    dt.Rows.Add(dr);
                }

            }

            return dt;
        }

    }

    public class ExcelDataRange
    {
        public ExcelDataRange()
        {

        }

        private bool _isFirstRowHeader = true;

        public bool IsFirstRowHeader
        {
            get { return _isFirstRowHeader; }
            set { _isFirstRowHeader = value; }
        }

        private int _columnBeginIndex = 0;

        public int ColumnBeginIndex
        {
            get { return _columnBeginIndex; }
            set { _columnBeginIndex = value; }
        }

        public int ColumnEndIndex { get; set; }
        private int _rowBeginIndex = 0;
        public int RowBeginIndex
        {
            get
            {
                return _rowBeginIndex;
            }
            set
            {
                _rowBeginIndex = value;
            }
        }

        public int RowEndIndex { get; set; }
    }
}
